<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>schema_analysis_views: common_schema documentation</title>
	<meta name="description" content="schema_analysis_views: common_schema" />
	<meta name="keywords" content="schema_analysis_views: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="schema_analysis_views.html">schema_analysis_views</a></h2>	
<h3>SYNOPSIS</h3>

<p>
Schema analysis views: a collection of views, analyzing schema design, listing design errors,
 generating SQL statements based on schema design
<ul>
	<li><a title="candidate_keys" href="candidate_keys.html">candidate_keys</a>: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.</li>
	<li><a title="candidate_keys_recommended" href="candidate_keys_recommended.html">candidate_keys_recommended</a>: Recommended candidate key per table.</li>
	<li><a title="no_pk_innodb_tables" href="no_pk_innodb_tables.html">no_pk_innodb_tables</a>: List InnoDB tables where no PRIMARY KEY is defined </li>
	<li><a title="redundant_keys" href="redundant_keys.html">redundant_keys</a>: List indexes which are made redundant (or duplicate) by other (dominant) keys. </li>
	<li><a title="routines" href="routines.html">routines</a>: Complement INFORMATION_SCHEMA.ROUTINES missing info.</li>
	<li><a title="sql_alter_table" href="sql_alter_table.html">sql_alter_table</a>: Generate ALTER TABLE SQL statements per table, with engine and create options</li>
	<li><a title="sql_foreign_keys" href="sql_foreign_keys.html">sql_foreign_keys</a>: Generate create/drop foreign key constraints SQL statements</li>
	<li><a title="sql_range_partitions" href="sql_range_partitions.html">sql_range_partitions</a>: Generate SQL statements for managing range partitions</li>
	<li><a title="table_charset" href="table_charset.html">table_charset</a>: List tables, their character sets and collations</li>
	<li><a title="text_columns" href="text_columns.html">text_columns</a>: List textual columns character sets & collations </li>
</ul>
</p>

<h3>DESCRIPTION</h3>
<p>Views in this category perform various schema analysis operations or offer SQL generation code cased on 
schema analysis.
</p>

<h3>EXAMPLES</h3>
<p>Detect duplicate keys on sakila.actor:</p>
<blockquote><pre>mysql&gt; ALTER TABLE `sakila`.`actor` ADD INDEX `actor_id_idx` (`actor_id`);
mysql&gt; ALTER TABLE `sakila`.`actor` ADD INDEX `last_and_first_names_idx` (`last_name`, `first_name`);
mysql&gt; ALTER TABLE `sakila`.`film_actor` ADD UNIQUE KEY `film_and_actor_ids_idx` (`film_id`, `actor_id`);

mysql&gt; SELECT * FROM common_schema.redundant_keys \G
*************************** 1. row ***************************
              table_schema: sakila
                table_name: actor
      redundant_index_name: idx_actor_last_name
   redundant_index_columns: last_name
redundant_index_non_unique: 1
       dominant_index_name: last_and_first_names_idx
    dominant_index_columns: last_name,first_name
 dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `sakila`.`actor` DROP INDEX `idx_actor_last_name`
*************************** 2. row ***************************
              table_schema: sakila
                table_name: actor
      redundant_index_name: actor_id_idx
   redundant_index_columns: actor_id
redundant_index_non_unique: 1
       dominant_index_name: PRIMARY
    dominant_index_columns: actor_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `sakila`.`actor` DROP INDEX `actor_id_idx`
*************************** 3. row ***************************
              table_schema: sakila
                table_name: film_actor
      redundant_index_name: idx_fk_film_id
   redundant_index_columns: film_id
redundant_index_non_unique: 1
       dominant_index_name: film_and_actor_ids_idx
    dominant_index_columns: film_id,actor_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `sakila`.`film_actor` DROP INDEX `idx_fk_film_id`
</pre></blockquote>


<p>Show recommended candidate keys for tables in <strong>sakila</strong></p>
<blockquote><pre>mysql&gt; SELECT * FROM common_schema.candidate_keys_recommended WHERE TABLE_SCHEMA='sakila';
+--------------+---------------+------------------------+--------------+------------+--------------+
| table_schema | table_name    | recommended_index_name | has_nullable | is_primary | column_names |
+--------------+---------------+------------------------+--------------+------------+--------------+
| sakila       | actor         | PRIMARY                |            0 |          1 | actor_id     |
| sakila       | address       | PRIMARY                |            0 |          1 | address_id   |
| sakila       | category      | PRIMARY                |            0 |          1 | category_id  |
| sakila       | city          | PRIMARY                |            0 |          1 | city_id      |
| sakila       | country       | PRIMARY                |            0 |          1 | country_id   |
| sakila       | customer      | PRIMARY                |            0 |          1 | customer_id  |
| sakila       | film          | PRIMARY                |            0 |          1 | film_id      |
| sakila       | film_actor    | PRIMARY                |            0 |          1 | actor_id     |
| sakila       | film_category | PRIMARY                |            0 |          1 | film_id      |
| sakila       | film_text     | PRIMARY                |            0 |          1 | film_id      |
| sakila       | inventory     | PRIMARY                |            0 |          1 | inventory_id |
| sakila       | language      | PRIMARY                |            0 |          1 | language_id  |
| sakila       | payment       | PRIMARY                |            0 |          1 | payment_id   |
| sakila       | rental        | PRIMARY                |            0 |          1 | rental_id    |
| sakila       | staff         | PRIMARY                |            0 |          1 | staff_id     |
| sakila       | store         | PRIMARY                |            0 |          1 | store_id     |
+--------------+---------------+------------------------+--------------+------------+--------------+
</pre></blockquote>


				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
